package InterfaceStaff;

import java.sql.*;
import java.util.*;
import InterfaceMahasiswa.*;
import javax.swing.JOptionPane;

public class Presensi {

    private Connection conn;
    private int jml;

    public Presensi(Connection koneksi) {
        this.conn = koneksi;
    }

    /*
     * method dibawah digunakan untuk mengambil koneksi ke database.
     */
    public static Presensi getKoneksi() throws SQLException {
        Presensi kon = new Presensi(tools.KoneksiDataBase.KoneksiDataBase());
        return kon;
    }

    /*
     * method dibawah digunakan untuk mengecek nim mahasiswa dengan menginputkan nim.
     */
    public void cekNIM(TabelPresensi mhs) throws SQLException {
        String NIM = mhs.getNim().getNIM();
        Statement st = null;
        ResultSet res = null;
        try {
            String query = "SELECT nim FROM mahasiswa where nim = '" + NIM + "'";
            st = conn.createStatement();
            res = st.executeQuery(query);

            if (res.next()) {
                insert(mhs);
                JOptionPane.showMessageDialog(null, "Data Masuk");
            } else {
                JOptionPane.showMessageDialog(null, "NIM tidak ada di database");
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
    }

    /*
     * method dibawah digunakan untuk menambah data presensi.
     */
    public void insert(TabelPresensi mhs) throws SQLException {
        String NIM = mhs.getNim().getNIM();
        String makul = mhs.getId_matakuliah().getMakul();
        String pengampu = mhs.getId_pengampu().getNama_pengampu();
        int jum = 0;
        int jum2 = 0;
        int jum3 = 0;

        String sql = "insert into"
                + " presensi (NIM, ID_MATAKULIAH, ID_PENGAMPU, TOTALKEHADIRAN, PERSEN, STATUS)"
                + " values('" + NIM + "',(select ID_MATAKULIAH from MATAKULIAH where NAMA_MATAKULIAH= '" + makul
                + "'),(select ID_PENGAMPU from PENGAMPU where NAMA_PENGAMPU= '" + pengampu + "'), '" + jum + "','"
                + jum2 + "','" + jum3 + "')";
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
    }

    /*
     * method dibawah digunakan untuk menghapus data presensi.
     */
    public void delete(Mahasiswa mhs, TabelPresensi tp) throws SQLException {
        String NIM = mhs.getNIM();
        String makul = tp.getId_matakuliah().getMakul();
        String pengampu = tp.getId_pengampu().getNama_pengampu();
        String sql = "delete from presensi"
                + " where NIM = '" + NIM + "' AND id_matakuliah = (select id_matakuliah from matakuliah where nama_matakuliah = '" + makul + "')"
                + "AND id_pengampu = (select id_pengampu from pengampu where nama_pengampu = '" + pengampu + "')";
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
    }

    /*
     * method dibawah digunakan untuk mengecek status mahasiswa tertentu.
     */
    public void cekStatus1(String nim) throws SQLException {
        Statement st = null;
        ResultSet res = null;
        try {
            String query = "SELECT status FROM presensi where status = 0 AND nim = '" + nim + "'";
            st = conn.createStatement();
            res = st.executeQuery(query);

            if (res.next()) {
                updateStatus1(nim);
            } else {
                JOptionPane.showMessageDialog(null, "Anda Sudah Presensi");
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
    }

    /*
     * method dibawah digunakan untuk mengupdate data mahasiswa.
     */
    public void updateStatus1(String nim) throws SQLException {
        String sql = "update presensi set status = 1 where NIM = '" + nim + "'";
        String sql2 = "update presensi set totalkehadiran = '" + getTambahKehadiran(nim) + "' where NIM = '" + nim + "'";
        Statement stat = conn.createStatement();
        Statement stat2 = conn.createStatement();
        stat.executeUpdate(sql);
        stat2.executeUpdate(sql2);
    }

    /*
     * method dibawah digunakan untuk menambah kehadiran mahasiswa tertentu.
     */
    public int getTambahKehadiran(String nim) throws SQLException {
        int id = 0;
        Statement st = null;
        ResultSet res = null;
        try {
            String query = "SELECT totalkehadiran FROM presensi where nim = '" + nim + "'";
            st = conn.createStatement();
            res = st.executeQuery(query);

            while (res.next()) {
                id = res.getInt(1) + 1;
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
            System.out.println("gagal");
        }
        return id;
    }

    /*
     * method dibawah digunakan untuk mengambil total kehadiran mahasiswa tertentu.
     */
    public int getTotalKehadiran(String nim) throws SQLException {
        int id = 0;
        Statement st = null;
        ResultSet res = null;
        try {
            String query = "SELECT totalkehadiran FROM presensi where nim = '" + nim + "'";
            st = conn.createStatement();
            res = st.executeQuery(query);

            while (res.next()) {
                id = res.getInt(1);
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return id;
    }

    /*
     * method dibawah digunakan untuk menghitung jumlah mahasiswa
     */
    public int jumlahMahasiswa(TabelPresensi mk) throws SQLException {
        int jml2 = 0;
        Statement st = null;
        ResultSet res = null;
        String makul = mk.getId_matakuliah().getMakul();
        String pengampu = mk.getId_pengampu().getNama_pengampu();

        try {
            String sql1 = "select count(NIM) from presensi"
                    + " where id_matakuliah = (select id_matakuliah from matakuliah where nama_matakuliah = '" + makul
                    + "') AND id_pengampu = (select id_pengampu from pengampu where nama_pengampu = '" + pengampu
                    + "')";

            st = conn.createStatement();
            res = st.executeQuery(sql1);
            while (res.next()) {
                jml2 = res.getInt(1);
            }
            System.out.println(makul);
            System.out.println(pengampu);
            System.out.println(jml2);
        } catch (SQLException sqle) {
            sqle.printStackTrace();
            System.out.println("gagal");
        }
        return jml2;
    }

    /*
     * method dibawah digunakan untuk mengupdate presentase mahasiswa mahasiswa tertentu
     *
     */
    public int persentase(String nim) throws SQLException {
        int persentase = 0;

        persentase = getTotalKehadiran(nim) * 100 / 14;
        if (persentase > 0) {
            return persentase;
        } else {
            return persentase = 0;
        }
    }

    /*
     * method dibawah digunakan untuk mengupdate presentase mahasiswa dalam mata kuliah
     * dan dosen tertentu.
     */
    public void updateAllPersentase(TabelPresensi tp) throws SQLException {
        PreparedStatement psmt = null;
        ResultSet rset = null;
        conn.setAutoCommit(false);
        String makul = tp.getId_matakuliah().getMakul();
        String pengampu = tp.getId_pengampu().getNama_pengampu();

        String sql = "select nim from presensi where id_matakuliah = (select id_matakuliah from matakuliah where nama_matakuliah = '" + makul
                + "') AND id_pengampu = (select id_pengampu from pengampu where nama_pengampu = '" + pengampu
                + "')";
        psmt = conn.prepareCall(sql);
        rset = psmt.executeQuery();
        while (rset.next()) {
            String sql2 = "update presensi set persen = '" + persentase(rset.getString(1)) + "' where NIM = '" + rset.getString(1) + "'";
            Statement stat = conn.createStatement();
            stat.executeUpdate(sql2);
        }
        conn.commit();

    }

    /*
     * method dibawah digunakan untuk mengambil semua mahasiswa yang terdaftar dalam presensi.
     */
    public List<Mahasiswa> getAllMahasiswa() throws SQLException {
        PreparedStatement psmt = null;
        ResultSet rset = null;
        conn.setAutoCommit(false);
        String sql = "select p.NIM, m.nama from presensi p, mahasiswa m"
                + " where p.NIM = m.NIM order by NIM";
        psmt = conn.prepareCall(sql);
        rset = psmt.executeQuery();
        List<Mahasiswa> mhs = new ArrayList<Mahasiswa>();
        while (rset.next()) {
            Mahasiswa k = new Mahasiswa();
            k.setNIM(rset.getString(1));
            k.setNama(rset.getString(2));
            mhs.add(k);
        }
        conn.commit();
        return mhs;
    }

    /*
     * method dibawah digunakan untuk mengambil semua mahasiswa yang terdaftar dalam presensi tertentu.
     */
    public List<Mahasiswa> getAllMahasiswaPresensi() throws SQLException {
        PreparedStatement psmt = null;
        ResultSet rset = null;
        conn.setAutoCommit(false);
        String sql = "select p.NIM, m.nama, p.status from presensi p, mahasiswa m"
                + " where p.NIM = m.NIM order by NIM";
        psmt = conn.prepareCall(sql);
        rset = psmt.executeQuery();
        List<Mahasiswa> mhs = new ArrayList<Mahasiswa>();
        while (rset.next()) {
            Mahasiswa k = new Mahasiswa();
            k.setNIM(rset.getString(1));
            k.setNama(rset.getString(2));
            k.setStatus(rset.getString(3));
            mhs.add(k);
        }
        conn.commit();
        return mhs;
    }

    /*
     * method dibawah digunakan untuk mengambil rekap semua mahasiswa yang terdaftar dalam presensi.
     */
    public List<TabelPresensi> getAllRekap() throws SQLException {
        PreparedStatement psmt = null;
        ResultSet rset = null;
        conn.setAutoCommit(false);
        String sql = "select NIM, totalkehadiran, persen from presensi order by NIM";
        psmt = conn.prepareCall(sql);
        rset = psmt.executeQuery();
        List<TabelPresensi> tp = new ArrayList<TabelPresensi>();
        while (rset.next()) {
            TabelPresensi k = new TabelPresensi();
            k.setNimmhs(rset.getString(1));
            k.setTotalKehadiran(rset.getString(2));
            k.setPersen(rset.getString(3));
            tp.add(k);
        }
        conn.commit();
        return tp;
    }

    /*
     * method dibawah digunakan untuk mengambil rekap semua mahasiswa yang terdaftar 
     * dalam presensi tertentu.
     */
    public List<TabelPresensi> getAllOpenRekap(TabelPresensi mk) throws SQLException {
        PreparedStatement psmt = null;
        ResultSet rset = null;
        conn.setAutoCommit(false);
        String makul = mk.getId_matakuliah().getMakul();
        String pengampu = mk.getId_pengampu().getNama_pengampu();

        String sql = "select NIM,totalkehadiran, persen from presensi where " + " id_matakuliah = (select id_matakuliah from matakuliah where nama_matakuliah = '" + makul
                + "') AND id_pengampu = (select id_pengampu from pengampu where nama_pengampu = '" + pengampu
                + "') order by NIM";
        psmt = conn.prepareCall(sql);
        rset = psmt.executeQuery();
        List<TabelPresensi> open = new ArrayList<TabelPresensi>();
        while (rset.next()) {
            TabelPresensi k = new TabelPresensi();
            k.setNimmhs(rset.getString(1));
            k.setTotalKehadiran(rset.getString(2));
            k.setPersen(rset.getString(3));
            open.add(k);
        }
        conn.commit();
        return open;
    }

    /*
     * method dibawah digunakan untuk mengambil data semua pengampu.
     */
    public List<Pengampu> getAllPengampu() throws SQLException {
        PreparedStatement psmt = null;
        ResultSet rset = null;
        conn.setAutoCommit(false);
        String sql = "select nama_pengampu from pengampu";
        psmt = conn.prepareCall(sql);
        rset = psmt.executeQuery();
        List<Pengampu> pengampu = new ArrayList<Pengampu>();
        while (rset.next()) {
            Pengampu k = new Pengampu();
            k.setNama_pengampu(rset.getString(1));
            pengampu.add(k);
        }
        conn.commit();
        return pengampu;
    }

    /*
     * method dibawah digunakan untuk mengambil data semua mata kuliah.
     */
    public List<MataKuliah> getAllMakul() throws SQLException {
        PreparedStatement psmt = null;
        ResultSet rset = null;
        conn.setAutoCommit(false);
        String sql = "select nama_matakuliah from matakuliah";
        psmt = conn.prepareCall(sql);
        rset = psmt.executeQuery();
        List<MataKuliah> makul = new ArrayList<MataKuliah>();
        while (rset.next()) {
            MataKuliah k = new MataKuliah();
            k.setMakul(rset.getString(1));
            makul.add(k);
        }
        conn.commit();
        return makul;
    }

    /*
     * method dibawah digunakan untuk membuka data semua mahasiswa yang terdaftar dalam presensi.
     */
    public List<Mahasiswa> getAllOpen(TabelPresensi mk) throws SQLException {
        PreparedStatement psmt = null;
        ResultSet rset = null;
        conn.setAutoCommit(false);
        String makul = mk.getId_matakuliah().getMakul();
        String pengampu = mk.getId_pengampu().getNama_pengampu();

        String sql = "select p.NIM, m.nama from presensi p, mahasiswa m"
                + " where p.nim = m.nim AND" + " p.id_matakuliah = (select id_matakuliah from matakuliah where nama_matakuliah = '" + makul
                + "') AND p.id_pengampu = (select id_pengampu from pengampu where nama_pengampu = '" + pengampu
                + "') order by NIM";
        psmt = conn.prepareCall(sql);
        rset = psmt.executeQuery();
        List<Mahasiswa> cari = new ArrayList<Mahasiswa>();
        while (rset.next()) {
            Mahasiswa k = new Mahasiswa();
            k.setNIM(rset.getString(1));
            k.setNama(rset.getString(2));
            cari.add(k);
        }
        conn.commit();
        return cari;
    }

    /*
     * method dibawah digunakan untuk membuka data semua mahasiswa yang terdaftar dalam presensi tertentu.
     */
    public List<Mahasiswa> getAllOpenPresensi(TabelPresensi mk) throws SQLException {
        PreparedStatement psmt = null;
        ResultSet rset = null;
        conn.setAutoCommit(false);
        String makul = mk.getId_matakuliah().getMakul();
        String pengampu = mk.getId_pengampu().getNama_pengampu();

        String sql = "select p.NIM, m.nama, p.status from presensi p, mahasiswa m"
                + " where p.nim = m.nim AND" + " p.id_matakuliah = (select id_matakuliah from matakuliah where nama_matakuliah = '" + makul
                + "') AND p.id_pengampu = (select id_pengampu from pengampu where nama_pengampu = '" + pengampu
                + "') order by NIM";
        psmt = conn.prepareCall(sql);
        rset = psmt.executeQuery();
        List<Mahasiswa> cari = new ArrayList<Mahasiswa>();
        while (rset.next()) {
            Mahasiswa k = new Mahasiswa();
            k.setNIM(rset.getString(1));
            k.setNama(rset.getString(2));
            k.setStatus(rset.getString(3));
            cari.add(k);
        }
        conn.commit();
        return cari;
    }

    /*
     * method dibawah digunakan untuk mereset status presensi menjadi 0.
     */
    public void resetStatus() throws SQLException {
        String sql = "update presensi set status = 0 where status = 1";
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        System.out.println("apap");
    }

    /*
     * method dibawah digunakan untuk mengambil nim.
     */
    public String ambilNim(String data) {
        String nim = null;
        Statement st = null;
        ResultSet res = null;
        try {
            String query = "SELECT nim FROM presensi where nim = '" + data + "'";
            st = conn.createStatement();
            res = st.executeQuery(query);

            while (res.next()) {
                nim = res.getString(1);
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
            System.out.println("gagal");
        }
        return nim;
    }

    /*
     * method dibawah digunakan untuk mengambil nama berdasarkan nim.
     */
    public String ambilNama(String data) {
        String nama = null;
        Statement st = null;
        ResultSet res = null;
        try {
            String query = "SELECT nama FROM mahasiswa where nim = '" + data + "'";
            st = conn.createStatement();
            res = st.executeQuery(query);

            while (res.next()) {
                nama = res.getString(1);
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
            System.out.println("gagal");
        }
        return nama;
    }

    /*
     * method dibawah digunakan untuk menghitung jumlah mahasiswa yang hadir.
     */
    public int ambilJumlahHadir(TabelPresensi mk) {
        int hadir = 0;
        Statement st = null;
        ResultSet res = null;
        String makul = mk.getId_matakuliah().getMakul();
        String pengampu = mk.getId_pengampu().getNama_pengampu();
        try {
            String query = "SELECT SUM(status) FROM presensi"
                    + " where id_matakuliah = (select id_matakuliah from matakuliah where nama_matakuliah = '" + makul
                    + "') AND id_pengampu = (select id_pengampu from pengampu where nama_pengampu = '" + pengampu + "')";
            st = conn.createStatement();
            res = st.executeQuery(query);

            while (res.next()) {
                hadir = res.getInt(1);
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
            System.out.println("gagal");
        }
        return hadir;
    }

    public int getJml() {
        return jml;
    }

    public void setJml(int jml) {
        this.jml = jml;
    }
}
